package echarttest;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;









import org.json.*;

//import net.sf.json.JSONException;
//import net.sf.json.JSONObject;


public class barDAO {
	public ResultSet rs;
	public ResultSet rs1;
	public PreparedStatement ps;
	public PreparedStatement ps1;
	public JSONObject select_all_key_value() throws SQLException {// ��λʱ���û����ӰƬ����
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://localhost:3306/bar?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "19970118");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT * from bar";
		   JSONObject array = new JSONObject();
		   System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		   List listname = new ArrayList();
		   List listid = new ArrayList();
		   int []id =new int[255];
		   String []name =new String[255];
		   String watchtime ="";
		   String avgwatchtime ="";
		   int i = 0;
		   while (rs.next()) { // ��ȡ���
		    id[i] = rs.getInt("id");
		    name[i] = rs.getString("name");
		    listname.add(name[i]);
		    listid.add(id[i]);
		    i++;
		   }
		   JSONObject jsonData = new JSONObject(); // ֱ�Ӱ�id ���û��� ��Ϊ json��ʽ
		   jsonData.put("name", listname);
		   jsonData.put("id", listid);
		   ps.close();
		   rs.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		   return jsonData;
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
		 }
	
	
	
	public JSONArray select_all_keys_values() throws SQLException {// ��λʱ���û����ӰƬ����
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://localhost:3306/bar?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "19970118");
//		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false";	
//		Connection conn = DriverManager.getConnection(url, "root", "root");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT * from bar";
		   JSONObject array = new JSONObject();
		   System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		    JSONArray jsonData=resultSetToJson(rs);
		   ps.close();
		   rs.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		   return jsonData;
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
		 }
	public JSONArray guo_select_all_keys_values() throws SQLException {// ��λʱ���û����ӰƬ����
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://localhost:3306/bar?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "19970118");
//		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false";	
//		Connection conn = DriverManager.getConnection(url, "root", "root");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT cityName,pv from result01";
		   JSONObject array = new JSONObject();
		   System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		    JSONArray jsonData=resultSetToJson(rs);
		   ps.close();
		   rs.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		  // System.out.println(jsonData.toString());
		   return jsonData;
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
		 }
	public JSONArray resultSetToJson(ResultSet rs) throws SQLException,JSONException  
	{  
	   // json����  
	   JSONArray array = new JSONArray();  
	    
	   // ��ȡ����  
	   ResultSetMetaData metaData = rs.getMetaData();  
	   int columnCount = metaData.getColumnCount();  
	    
	   // ����ResultSet�е�ÿ�����  
	    while (rs.next()) {  
	        JSONObject jsonObj = new JSONObject();  
	         
	        // ����ÿһ��  
	        for (int i = 1; i <= columnCount; i++) {  
	            String columnName =metaData.getColumnLabel(i);  
	           // System.out.println(columnName);
	            String value = rs.getString(columnName);  
	            //System.out.println(value);
	            jsonObj.put(columnName, value);  
	        }   
	        array.put(jsonObj);
	        
	     
	    }  
	    return array;
	}
	    
	    public JSONArray resultSetToDoubleJson(ResultSet rs,ResultSet rs1) throws SQLException,JSONException  
		{  
		   // json����  
		   JSONArray array = new JSONArray();  
		    
		   // ��ȡ����  
		   ResultSetMetaData metaData = rs.getMetaData();  
		   ResultSetMetaData metaData1 = rs1.getMetaData();  
		   int columnCount = metaData.getColumnCount();  
		   int columnCount1 = metaData1.getColumnCount();  
		    
		   // ����ResultSet�е�ÿ�����  
		    while (rs.next()&&rs1.next()) {  
		        JSONObject jsonObj = new JSONObject();  
		         
		        // ����ÿһ��  
		        for (int i = 1; i <= columnCount; i++) {  
		            String columnName =metaData.getColumnLabel(i);  
		           // System.out.println(columnName);
		            String value = rs.getString(columnName);  
		           // System.out.println(value);
		            jsonObj.put(columnName, value);  
		        } 
		        for (int i = 1; i <= columnCount1; i++) {  
		            String columnName1 =metaData1.getColumnLabel(i);  
		           // System.out.println(columnName1);
		            if(jsonObj.has(columnName1)){
		            	continue;
		            }
		           
		            String value1 = rs1.getString(columnName1);  
		           // System.out.println(value1);
		            jsonObj.put(columnName1, value1);  
		        }   
		        array.put(jsonObj);
		        
		     
		    }  
		    
//	 String string=array.toString();
//	 int  length=string.length();
//	 String substring=(String) string.subSequence(1, length-1);
//	 System.out.println(substring);
	    
	   return  array;  
	}  
	public void PositionToSQL() throws SQLException, UnsupportedEncodingException{
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://localhost:3306/bar?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "19970118");
//		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false";	
//		Connection conn = DriverManager.getConnection(url, "root", "root");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT cityName from result01";
		   JSONObject array = new JSONObject();
		   //System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		    JSONArray jsonData=resultSetToJson(rs);
		    for(int i=0;i<jsonData.length();i++){
		    	JSONObject ob  = (JSONObject) jsonData.get(i);//
		    	String  name= ob.getString("cityName");//ֵ
		    	Map<String,Double> map=BaiduApi.getLngAndLat(name);
		    	System.out.println(name);//
		    	//String str = new String(name.getBytes(), "utf-8"); 
		    	String updatesql="Insert into city_position (cityName,lng,lat) values ('"+name+"',"+map.get("lng")+","+map.get("lat")+")";
		    	//System.out.println(updatesql);
		    	ps.executeUpdate(updatesql);
		    }
		   
		   ps.close();
		   rs.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		  // System.out.println(jsonData.toString());
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
	}
	public JSONArray PositionFromSQL() throws SQLException{
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://localhost:3306/bar?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "19970118");
//		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false";	
//		Connection conn = DriverManager.getConnection(url, "root", "root");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT cityName,lng,lat from city_position";
		   String sql1 = "SELECT cityName,pv from result01";
		   JSONObject array = new JSONObject();
		   System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		   ps1=conn.prepareStatement(sql1);
		   rs1=ps1.executeQuery();
		    JSONArray jsonData=resultSetToDoubleJson(rs,rs1);
		   ps.close();
		   rs.close();
		   ps1.close();
		   rs1.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		  // System.out.println(jsonData.toString());
		   return jsonData;
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
	}
	public JSONArray select_all_phones() throws SQLException {// ��λʱ���û����ӰƬ����
		Properties properties=System.getProperties();
		System.out.println(properties.getProperty("file.encoding"));
        try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//һ��ʼ������һ���Ѿ����ڵ���ݿ�
		String url = "jdbc:mysql://192.168.1.105:3306/test?characterEncoding=utf-8&useSSL=false";	
		Connection conn = DriverManager.getConnection(url, "root", "root");
//		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false";	
//		Connection conn = DriverManager.getConnection(url, "root", "root");
		
		  System.out.println("������ݿ�����");
		  try {
		   String sql = "SELECT * from modeltrend";
		   JSONObject array = new JSONObject();
		   System.out.println("����sqlִ��");
		   ps = conn.prepareStatement(sql);
		   rs = ps.executeQuery();
		    JSONArray jsonData=resultSetToJson(rs);
		   ps.close();
		   rs.close();
		   if (ps != null)
		    ps.close();
		   if (rs != null)
		    rs.close();
		   if (conn != null)
		    conn.close();
		  // System.out.println(jsonData.toString());
		   return jsonData;
		  }
		  finally {
		   try {
		    conn.close();
		   } catch (SQLException e) {
		    System.out
		    .println("Error occured at closing connection in barDAO");
		   }
		  }
		 }
	public static void main(String args[]) throws UnsupportedEncodingException{
		barDAO dao=new barDAO();
		try {
			dao.select_all_phones();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
